package com.b2c.repository.erp;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.ErpOrderEntity;
import com.b2c.entity.ErpOrderItemEntity;
import com.b2c.entity.StockDataVo;
import com.b2c.entity.erp.ErpGoodsStockInfoItemEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.erp.enums.StockOutFormStatusEnum;
import com.b2c.entity.erp.vo.ErpStockOutGoodsListVo;
import com.b2c.entity.erp.vo.ErpStockOutGoodsStockInfo;
import com.b2c.entity.xhs.XhsOrderEntity;
import com.b2c.entity.xhs.XhsOrderItemEntity;
import com.b2c.entity.enums.EnumXhsOrderStatus;
import com.b2c.entity.ErpStockOutFormEntity;
import com.b2c.entity.ErpStockOutFormItemEntity;
import com.b2c.entity.ErpStockOutPickVo;
import com.b2c.repository.Tables;
import com.b2c.repository.utils.OrderNumberUtils;

import com.b2c.entity.enums.EnumErpOrderSendStatus;
import com.b2c.entity.enums.erp.EnumGoodsStockLogSourceType;
import com.b2c.entity.enums.erp.EnumGoodsStockLogType;
import com.b2c.entity.vo.ErpStockOutFormDetailVo;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * 描述：
 * ERP出库商品Repository
 *
 * @author qlp
 * @date 2019-06-21 13:29
 */
@Repository
public class ErpStockOutFormRepository {
    @Autowired
    protected JdbcTemplate jdbcTemplate;
    @Autowired
    private ErpGoodsRepository erpGoodsRepository;
    Logger log = LoggerFactory.getLogger(ErpStockOutFormRepository.class);
    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }



    /**
     * 获取拣货单列表
     *
     * @param pageIndex
     * @param pageSize
     * @param No
     * @return
     */
    public PagingResponse<ErpStockOutFormEntity> getPickingList(Integer pageIndex, Integer pageSize, String No, Integer status, Long id,String sourceNo) {

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT SQL_CALC_FOUND_ROWS A.*, ");
        sql.append("(SELECT SUM(quantity) FROM ").append(Tables.ErpStockOutFormItem).append(" WHERE formId=A.id and `status` <> 99) as goodsCount,");//查询商品数量
        sql.append("(SELECT COUNT(0) FROM ").append(Tables.ErpStockOutFormItem).append(" WHERE formId=A.id and `status` <> 99) as orderCount");//查询子订单数量
//        sql.append("(select count(distinct orderId) from "+Tables.ErpOrderItem+" group by orderId ) as orderCount");//订单数量
//        sql.append("(select count(*) from (select distinct orderId,stockOutFormId from " + Tables.ErpOrderItem + " group by orderId)C WHERE stockOutFormId=A.id  ) as orderCount");
        sql.append(" FROM ").append(Tables.ErpStockOutForm).append(" as A WHERE outType=1 AND isDelete =0 ");
        List<Object> params = new ArrayList<>();

        if(id != null && id >0){
            sql.append("AND A.id=? ");
            params.add(id);
        }else {

            if (StringUtils.isEmpty(No) == false) {
                sql.append("AND stockOutNo=? ");
                params.add(No);
            }
            if (StringUtils.isEmpty(sourceNo) == false) {
                sql.append("AND sourceNo=? ");
                params.add(sourceNo);
            }
            if (status != null) {
                if(status.intValue() == 0){
                    sql.append(" AND  (status=0 OR status=1) ");

                }else {
                    sql.append(" AND  status=? ");
                    params.add(status);
                }
            } else {
                sql.append(" AND  status <> 3 ");
            }
        }
        sql.append(" ORDER BY A.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ErpStockOutFormEntity.class), params.toArray(new Object[params.size()]));

        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * 根据主键获取表单
     *
     * @param id
     * @return
     */
    public ErpStockOutFormEntity getStockOutFormById(Long id) {
        try {
            String sql = "SELECT * FROM " + Tables.ErpStockOutForm + " WHERE id=?";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErpStockOutFormEntity.class), id);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 根据单号获取表单
     *
     * @param stockOutNo
     * @return
     */
    public ErpStockOutFormEntity getStockOutFormByNumber(String stockOutNo) {
        String sql = "SELECT * FROM " + Tables.ErpStockOutForm + " WHERE stockOutNo=? ";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErpStockOutFormEntity.class), stockOutNo);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 根据拣货单id获取拣货单商品list
     *
     * @param stockOutId
     * @return
     */
    public List<ErpStockOutGoodsListVo> getStockOutGoodsForOrderByFormId(Long stockOutId) {

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT  oitem.id as orderItemId,");
        sql.append("g.name as goodsName,g.number as productNumber,gs.specNumber as skuNumber");
        sql.append(",gs.color_value,gs.size_value,gs.style_value,gs.color_image ");
        sql.append(",o.order_num,ofi.goodsId,ofi.specId,ofi.`status` ");
        sql.append(",sum(ofi.quantity) AS quantity");
        sql.append(",s.number as locationName");
        sql.append(",(SELECT l.number from erp_goods_stock_info s LEFT JOIN erp_stock_location l on  s.locationId=l.id WHERE s.isDelete=0 and s.specId=ofi.specId limit 1) as localNumber ");

        sql.append(" FROM ").append(Tables.ErpStockOutFormItem).append(" as ofi ");
        sql.append(" LEFT JOIN ").append(Tables.ErpOrderItem).append(" as oitem on oitem.id = ofi.itemId ");
        sql.append(" LEFT JOIN ").append(Tables.ErpOrder).append(" as o on o.id=oitem.orderId ");
        sql.append(" LEFT JOIN " + Tables.ErpGoodsSpec).append(" as gs on gs.id=ofi.specId ");
        sql.append(" LEFT JOIN " + Tables.ErpGoods).append(" as g on g.id=ofi.goodsId ");
        sql.append(" LEFT JOIN " + Tables.ErpStockLocation).append(" as s on s.id=ofi.locationId ");

        sql.append(" WHERE ofi.formId=? AND ofi.status <> 99");
        sql.append(" GROUP BY ofi.specId ");
        sql.append(" ORDER BY localNumber asc ");


        //拣货单中的商品
        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ErpStockOutGoodsListVo.class), stockOutId);

        /*********循环查询库存信息**********/
        for (var item : list) {
            //查询商品规格库存 erp_goods_stock_info
            StringBuilder sb = new StringBuilder();
            sb.append(" SELECT gs.currentQty,gs.lockedQty,gs.locationId,s.name as locationName FROM ");
            sb.append(Tables.ErpGoodsStockInfo).append(" as gs");
            sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" s on s.id=gs.locationId");
//            sb.append(" LEFT JOIN ").append(Tables.ErpStoreHouse).append(" sh on sh.id=gs.locationId");
//            sb.append(" LEFT JOIN ").append(Tables.ErpReservoir).append(" r on r.id=gs.reservoirId");
            sb.append(" WHERE gs.specId=? AND gs.isDelete=0");

            var stockList = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpStockOutGoodsStockInfo.class), item.getSpecId());
            //库存显示
            item.setStocks(stockList);
        }

        return list;
    }

    public List<ErpStockOutGoodsListVo> getStockOutFormItemListByFormId(Long stockOutId) {

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT  ");
        sql.append("g.`name` as goodsName,g.number as productNumber,gs.specNumber as skuNumber");
        sql.append(",gs.color_value,gs.size_value,gs.style_value,gs.color_image ");
        sql.append(",ofi.goodsId,ofi.specId,ofi.`status` ");
        sql.append(",ofi.quantity,ofi.`status`,ofi.sourceChannel,ofi.sourceOrderNo ");
        sql.append(" FROM ").append(Tables.ErpStockOutFormItem).append(" as ofi ");
        sql.append(" LEFT JOIN " + Tables.ErpGoodsSpec).append(" as gs on gs.id=ofi.specId ");
        sql.append(" LEFT JOIN " + Tables.ErpGoods).append(" as g on g.id = gs.goodsId ");

        sql.append(" WHERE ofi.formId=? AND ofi.status <> 99");




        //拣货单中的商品
        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ErpStockOutGoodsListVo.class), stockOutId);

        /*********循环查询库存信息**********/
        for (var item : list) {
            //查询商品规格库存 erp_goods_stock_info
            StringBuilder sb = new StringBuilder();
            sb.append(" SELECT gs.currentQty,gs.lockedQty,gs.locationId,s.name as locationName FROM ");
            sb.append(Tables.ErpGoodsStockInfo).append(" as gs");
            sb.append(" LEFT JOIN ").append(Tables.ErpStockLocation).append(" s on s.id=gs.locationId");
//            sb.append(" LEFT JOIN ").append(Tables.ErpStoreHouse).append(" sh on sh.id=gs.locationId");
//            sb.append(" LEFT JOIN ").append(Tables.ErpReservoir).append(" r on r.id=gs.reservoirId");
            sb.append(" WHERE gs.specId=? AND gs.isDelete=0");

            var stockList = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpStockOutGoodsStockInfo.class), item.getSpecId());
            //库存显示
            item.setStocks(stockList);
        }

        return list;
    }

    /**
     * 更新拣货单为已打印
     *
     * @param id
     * @return
     */
    public Integer printPicking(Long id) {
        var s = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.ErpStockOutForm+" WHERE id=?",new BeanPropertyRowMapper<>(ErpStockOutFormEntity.class),id);
        if(s.getStatus().intValue() == 0){
            String sql = "UPDATE " + Tables.ErpStockOutForm + " SET printStatus=?,printTime=?,`status`=1 WHERE id=?";
            return jdbcTemplate.update(sql, 1, System.currentTimeMillis() / 1000, id);
        } else {
            String sql = "UPDATE " + Tables.ErpStockOutForm + " SET printStatus=?,printTime=? WHERE id=?";
            return jdbcTemplate.update(sql, 1, System.currentTimeMillis() / 1000, id);
        }
    }

    /**
     * 确认拣货单，更新拣货单状态为已拣货
     *
     * @param orderId 订单ID
     * @param userId         当前操作的userid
     * @param userName       当前操作人
     * @return
     */

    @Transactional
    public ResultVo<Integer> orderGoodsStockOut(Long orderId, Integer userId, String userName) {
        /*********查询订单信息*********/
        ErpOrderEntity order=null;
        try {
            order = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpOrder + " WHERE id=?", new BeanPropertyRowMapper<>(ErpOrderEntity.class), orderId);
            if (order.getStatus().intValue() != 1) {
                return new ResultVo<>(EnumResultVo.DataError, order.getOrder_num() + "不是拣货中订单,订单ID：" + orderId);
            }
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.DataError, "数据错误" + e.getMessage());
        }
        var orderItems = jdbcTemplate.query("SELECT * FROM " + Tables.ErpOrderItem + " WHERE orderId=?", new BeanPropertyRowMapper<>(ErpOrderItemEntity.class), orderId);

        if (orderItems == null || orderItems.size() == 0)
            return new ResultVo<>(EnumResultVo.ParamsError, "找不到订单items，OrderId：" + orderId);

//        List<Long> orderItemIds = orderItems.stream().map(o->o.getId()).collect(Collectors.toList());

//        ErpStockOutFormEntity stockOutForm = null;
//        try {
//            String stockOutFormSQL = "SELECT * FROM " + Tables.ErpStockOutForm + " where id=?";
//            stockOutForm = jdbcTemplate.queryForObject(stockOutFormSQL, new BeanPropertyRowMapper<>(ErpStockOutFormEntity.class), stockOutFormId);
//            if (stockOutForm.getStatus().intValue() != StockOutFormStatusEnum.Packing.getIndex()) return 0;
//        } catch (Exception e) {
//            return -404;//拣货单不存在
//        }


        /************1、更新订单明细erp_order_item捡货状态*************/
        for (var item : orderItems) {
            //查询仓位库存信息(查出所有仓位库存，以备减库存用)
            var goodsStockInfos = jdbcTemplate.query("SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE specId=? AND isDelete = 0"
                    , new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), item.getSkuId());
            if (goodsStockInfos == null || goodsStockInfos.size() == 0) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return new ResultVo<>(EnumResultVo.Locking, "仓位库存信息不存在，SKU：" + item.getSkuNumber());
            }

            Double currentQty = goodsStockInfos.stream().mapToDouble(ErpGoodsStockInfoEntity::getCurrentQty).sum();//当前库存总和

            if (currentQty.longValue() < item.getQuantity()) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
                return new ResultVo<>(EnumResultVo.NumberLess, "仓位可用库存不足，SKU：" + item.getSkuNumber());
            }

            /********判断子订单状态********/
            if(item.getStatus().intValue() != EnumErpOrderSendStatus.Picked.getIndex()){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
                return new ResultVo<>(EnumResultVo.StatusError, "子订单" + item.getId()+"状态不正确，状态值："+item.getStatus()+"，订单ID："+item.getOrderId());
            }


            /****************开始扣减库存，并更新状态*****************/
            ErpGoodsStockInfoEntity stockInfo = goodsStockInfos.get(0);
            if (stockInfo.getCurrentQty() < item.getQuantity()) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
                return new ResultVo<>(EnumResultVo.Locking, "仓位ID" + stockInfo.getLocationId() + "可用库存不足，SKU：" + item.getSkuNumber());
            }

            //扣减的库存id locationId
            Integer locationId = stockInfo.getLocationId();

            /********判断拣货单明细状态erp_stock_out_form_item********/
            var stockOutFormItems = jdbcTemplate.query("SELECT * FROM "+Tables.ErpStockOutFormItem + " WHERE itemId=? and formId=?",new BeanPropertyRowMapper<>(ErpStockOutFormItemEntity.class),item.getId(),item.getStockOutFormId());
            if(stockOutFormItems == null || stockOutFormItems.size()==0){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
                return new ResultVo<>(EnumResultVo.DataError, "不存在拣货单信息，itemId：" + item.getId());
            }else  if(stockOutFormItems.get(0).getStatus().intValue() != EnumErpOrderSendStatus.Picked.getIndex()){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
                return new ResultVo<>(EnumResultVo.StatusError, "拣货单明细状态不正确，拣货单明细ID：" + stockOutFormItems.get(0).getId()+"，状态值："+stockOutFormItems.get(0).getStatus());
            }
            Long stockOutFormId = stockOutFormItems.get(0).getFormId();
            Long stockOutFormItemId = stockOutFormItems.get(0).getId();
            try {
                /******************更新出库明细 erp_stock_out_form_item(更新成已出库)**********************/
                jdbcTemplate.update("UPDATE erp_stock_out_form_item SET locationId=?,completeTime=?,status=? WHERE id=?"
                        , locationId, System.currentTimeMillis() / 1000, 3, stockOutFormItemId);

                /********2.1、更新商品库存_erp_goods_spec***********/
                jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET currentQty=currentQty-? WHERE id=?", item.getQuantity(), item.getSkuId());

                /********2.2、更新商品仓位库存_erp_goods_stock_info***********/
                String updErpGoodsStock = "UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty-? WHERE specId=? AND locationId=? AND isDelete=0";
                jdbcTemplate.update(updErpGoodsStock, item.getQuantity(), item.getSkuId(), locationId);

                var goodsStockInfoItems = jdbcTemplate.query("SELECT * FROM erp_goods_stock_info_item WHERE stockInfoId=? and currentQty>0 ", new BeanPropertyRowMapper<>(ErpGoodsStockInfoItemEntity.class), stockInfo.getId());
                /**********处理先进先出问题**********/
                if (goodsStockInfoItems.size() > 0) {
                    Long quantity = item.getQuantity();//购买数量
                    ErpGoodsStockInfoItemEntity stockInfoItem = goodsStockInfoItems.get(0);
                    //数量小于库存 库存-数量
                    if (stockInfoItem.getCurrentQty() >= quantity) {
                        //更新goodsStockInfoItem
                        jdbcTemplate.update("update erp_goods_stock_info_item set currentQty=currentQty-? where id=?", quantity, stockInfoItem.getId());

                        //更新仓位id到orderItem
                        String orderItemUpdateSQL = "UPDATE " + Tables.ErpOrderItem + " SET status=?,stockOutFormId=?,outLocationId=?,purPrice=?,stockInfoItemId=? WHERE id=?";
                        jdbcTemplate.update(orderItemUpdateSQL, EnumErpOrderSendStatus.HasOut.getIndex(), stockOutFormId, locationId, stockInfoItem.getPurPrice(), stockInfoItem.getId(), item.getId());

                        //插入erp_stock_out_form_item_detail数据
                        jdbcTemplate.update("INSERT INTO erp_stock_out_form_item_detail (stock_out_form_item_id,goods_stock_info_id,goods_stock_info_item_id,quantity) VALUE (?,?,?,?)", stockOutFormItemId,goodsStockInfoItems.get(0).getStockInfoId(),goodsStockInfoItems.get(0).getId(),quantity);

                    } else {
                        Long cutQty = 0L;//已减库存
                        // String addErpOrderItemSQL = "insert into erp_order_item set orderId=?,productId=?,aliSubItemID=?,productMallName=?,productNumber=?,skuNumber=?,skuId=?" +
                        //         ",productImgUrl=?,quantity=?,status=?,itemAmount=?,price=?,purPrice=?,stockOutFormId=?,outLocationId=?,stockInfoItemId=?";
                        //更新仓位id到orderItem
                        String orderItemUpdateSQL = "UPDATE " + Tables.ErpOrderItem + " SET status=?,stockOutFormId=?,outLocationId=?,purPrice=? WHERE id=?";
                        jdbcTemplate.update(orderItemUpdateSQL, EnumErpOrderSendStatus.HasOut.getIndex(), stockOutFormId, locationId,stockInfoItem.getPurPrice(),item.getId());
                        for (var stockItem : goodsStockInfoItems) {
                            cutQty += stockItem.getCurrentQty();
                            if (quantity > cutQty) {
                                jdbcTemplate.update("update erp_goods_stock_info_item set currentQty=currentQty-? where id=?", stockItem.getCurrentQty(), stockItem.getId());
                                //插入erp_stock_out_form_item_detail数据
                                jdbcTemplate.update("INSERT INTO erp_stock_out_form_item_detail (stock_out_form_item_id,goods_stock_info_id,goods_stock_info_item_id,quantity) VALUE (?,?,?,?)", stockOutFormItemId,stockItem.getStockInfoId(),stockItem.getId(),stockItem.getCurrentQty());
          /*                      jdbcTemplate.update(addErpOrderItemSQL, item.getOrderId(), item.getProductId(), item.getAliSubItemID(), item.getProductMallName(), item.getProductNumber(),
                                        item.getSkuNumber(), item.getSkuId(), item.getProductImgUrl(), stockItem.getCurrentQty(), EnumErpOrderSendStatus.HasOut.getIndex(),
                                        stockItem.getCurrentQty() * item.getPrice(), item.getPrice(), stockItem.getPurPrice(), stockOutFormId, locationId, stockItem.getId());*/
                            } else {
                                Long cutQty_ = quantity - (cutQty - stockItem.getCurrentQty());
                                jdbcTemplate.update("update erp_goods_stock_info_item set currentQty=currentQty-? where id=?", cutQty_, stockItem.getId());
                                //插入erp_stock_out_form_item_detail数据
                                jdbcTemplate.update("INSERT INTO erp_stock_out_form_item_detail (stock_out_form_item_id,goods_stock_info_id,goods_stock_info_item_id,quantity) VALUE (?,?,?,?)", stockOutFormItemId,stockItem.getStockInfoId(),stockItem.getId(),cutQty_);
                              /*  jdbcTemplate.update(addErpOrderItemSQL, item.getOrderId(), item.getProductId(), item.getAliSubItemID(), item.getProductMallName(), item.getProductNumber(),
                                        item.getSkuNumber(), item.getSkuId(), item.getProductImgUrl(), cutQty_, EnumErpOrderSendStatus.HasOut.getIndex(),
                                        cutQty_ * item.getPrice(), item.getPrice(), stockItem.getPurPrice(), stockOutFormId, locationId, stockItem.getId());*/
                                break;
                            }
                        }
                    }
                } else {
                    String orderItemUpdateSQL = "UPDATE " + Tables.ErpOrderItem + " SET status=?,stockOutFormId=?,outLocationId=?,purPrice=?,stockInfoItemId=? WHERE id=?";
                    jdbcTemplate.update(orderItemUpdateSQL, EnumErpOrderSendStatus.HasOut.getIndex(), stockOutFormId, locationId, 0, 0, item.getId());
                }

                /*************  2.3、加入库存日志erp_goods_stock_logs*****************/
                String remark = "订单商品出库，orderItemID：" + item.getId();
                remark += " SKU：" + item.getSkuNumber();
                remark += " 数量：" + item.getQuantity();
                remark += " 仓位ID：" + locationId;
                remark += "操作人:" + userId + " " + userName;
                String logsSQL = "INSERT INTO " + Tables.ErpGoodsStockLogs +
                        " (goodsId,goodsNumber,specId,specNumber,locationId,quantity,createTime,type,sourceType,sourceId,remark,createOn,currQty) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
                jdbcTemplate.update(logsSQL,
                        item.getProductId()
                        , item.getProductNumber()
                        , item.getSkuId()
                        , item.getSkuNumber()
                        , locationId, item.getQuantity(),
                        new Date(), EnumGoodsStockLogType.OUT.getIndex(), EnumGoodsStockLogSourceType.OrderSend.getIndex(), item.getId()
                        , remark, System.currentTimeMillis() / 1000, goodsStockInfos.get(0).getCurrentQty().longValue() - item.getQuantity().longValue());


                /*************更新对应的订单状态******************/
                // (查询有没有未完成拣货的，状态为Wait或Picking或Picked) 根据订单号查询
                String infoSQL = "SELECT COUNT(0) FROM " + Tables.ErpOrderItem + " WHERE orderId=? AND (status=? OR status=? OR status=?)";

                int count = jdbcTemplate.queryForObject(infoSQL, Integer.class, item.getOrderId(), StockOutFormStatusEnum.WAIT.getIndex(), StockOutFormStatusEnum.Packing.getIndex(), StockOutFormStatusEnum.Picked.getIndex());
                if (count == 0) {
                    //都完成了出库，更新orders 状态为已出库
                    String updateSQL = "UPDATE " + Tables.ErpOrder + " SET status=?,stockOutTime=?,modifyTime=? WHERE id=?";
                    jdbcTemplate.update(updateSQL, EnumErpOrderSendStatus.HasSend.getIndex(), System.currentTimeMillis() / 1000, System.currentTimeMillis() / 1000, item.getOrderId());
                    if(order.getShopId().intValue()==99){
                        String updateSQL99 = "UPDATE " + Tables.ErpSalesOrder + " SET status=? WHERE orderNum=?";
                        jdbcTemplate.update(updateSQL99, EnumErpOrderSendStatus.HasSend.getIndex(),order.getOrder_num());
                    }
                }

                /*****1、更新拣货单erp_stock_out_form状态*****/
                int count1 = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM " + Tables.ErpStockOutFormItem + " WHERE formId=? AND status <> 3 AND status <> 99", Integer.class, item.getStockOutFormId());
                if (count1 == 0) {
                    //更新拣货单状态
                    jdbcTemplate.update("UPDATE " + Tables.ErpStockOutForm + " SET status=?,completeTime=?,stockOutUserId=?,stockOutUserName=? WHERE id=?",
                            StockOutFormStatusEnum.OUTED.getIndex(),
                            System.currentTimeMillis() / 1000,
                            userId, userName,
                            stockOutFormId);
                    //TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                }


            } catch (Exception e) {
                //系统异常
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return new ResultVo<>(EnumResultVo.SystemException, "系统异常" + e.getMessage());
            }
        }

//        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }


    /**
     * 出库单列表
     *
     * @param pageIndex
     * @param pageSize
     * @param number
     * @return
     */
    public PagingResponse<ErpStockOutFormEntity> getStockOutFormList(Integer pageIndex, Integer pageSize,Integer outType, String number, Long start, Long end) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS a.*," +
                "(SELECT SUM(quantity) FROM " + Tables.ErpStockOutFormItem + " WHERE formId=a.id and status=3) as quantity  " +
                "FROM " + Tables.ErpStockOutForm + " a where 1=1 ";
        List<Object> params = new ArrayList<>();
        sql += " AND (a.status=? OR a.status=?) ";
        params.add(StockOutFormStatusEnum.Picked.getIndex());
        params.add(StockOutFormStatusEnum.OUTED.getIndex());

        if(outType!=null && outType>0){
            sql += " AND a.outType = ? ";
            params.add(outType);
        }

        if (StringUtils.isEmpty(number) == false) {
            sql += " AND a.stockOutNo=? ";
            params.add(number);
        }
        if (start > 0) {
            sql += " AND completeTime >= ? ";
            params.add(start);

        }
        if (end > 0) {
            sql += " AND completeTime <= ? ";
            params.add(end);
        }
        sql += "ORDER BY a.id DESC LIMIT ?,?";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockOutFormEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);

    }

    /**
     * 出库单记录
     *
     * @param id
     * @return
     */
    public ErpStockOutFormDetailVo getErpStockOutFormDetailVo(Long id) {
        ErpStockOutFormDetailVo list = jdbcTemplate.queryForObject("SELECT SQL_CALC_FOUND_ROWS id,stockOutNo,completeTime,createBy FROM erp_stock_out_form WHERE id=? ", new BeanPropertyRowMapper<>(ErpStockOutFormDetailVo.class), id);
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.*,");

        sb.append("B.`name` locationName,C.specNumber,C.color_value,C.size_value,C.color_image ");
        sb.append(",(SELECT GROUP_CONCAT(goods_stock_info_item_id) FROM erp_stock_out_form_item_detail WHERE stock_out_form_item_id = A.id) AS stockOutFormItemId");
        sb.append(" FROM erp_stock_out_form_item A ");
        sb.append(" LEFT JOIN erp_stock_location B ON A.locationId=B.id ");
        sb.append(" LEFT JOIN erp_goods_spec C ON A.specId=C.id ");

        sb.append("WHERE A.formId=?");
        sb.append(" ORDER BY A.status asc ");

        list.setItems(jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpStockOutFormItemEntity.class), list.getId()));
        return list;
    }



    /**
     * 取消拣货
     * @param stockOutFormItemId
     * @return
     */
    @Transactional
    public ResultVo<Integer> cancelStockOut(Long stockOutFormItemId,Integer userId, String userName) {
        try {
            log.info("取消拣货,stockOutFormItemId"+stockOutFormItemId);
            var stockOutFormItem = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.ErpStockOutFormItem +" WHERE id=?",new BeanPropertyRowMapper<>(ErpStockOutFormItemEntity.class),stockOutFormItemId);
            if(stockOutFormItem.getStatus().intValue() != 1){
                log.info("数据错误，拣货单item状态为："+stockOutFormItem.getStatus()+"，不能操作");
                return new ResultVo<>(EnumResultVo.DataError, "数据错误，拣货单item状态为："+stockOutFormItem.getStatus()+"，不能操作");
            }

            //查询itemId对应的订单id
            var orderId = jdbcTemplate.queryForObject("SELECT orderId FROM "+Tables.ErpOrderItem +" WHERE id=?",long.class,stockOutFormItem.getSourceOrderItemId());

            //查询itemId对应的订单item
            var orderItemList = jdbcTemplate.query("SELECT * FROM "+Tables.ErpOrderItem +" WHERE orderId=?",new BeanPropertyRowMapper<>(ErpOrderItemEntity.class),orderId);

            //查询对应有几条出库单数据，并且判断状态
//            var stockOutFormItemList =jdbcTemplate.query("SELECT * FROM "+Tables.ErpStockOutFormItem + " WHERE formId=? AND itemId=? ",
//                    new BeanPropertyRowMapper<>(ErpStockOutFormItemEntity.class),stockOutFormItem.getFormId(),stockOutFormItem.getItemId());

            /**********循环更新状态***********/
            for (var orderItem :orderItemList) {

                /*************一、更新拣货单明细 stock_out_form_item 状态为已取消 (同一个订单的所有itemId)***************/
                jdbcTemplate.update("UPDATE "+Tables.ErpStockOutFormItem +" SET status = 99 WHERE formId=? AND status =1 AND itemId=?",stockOutFormItem.getFormId(),orderItem.getId());
//                if(stockOutFormItem1.getStatus().intValue() != 1){
//                    return new ResultVo<>(EnumResultVo.DataError, "数据错误，拣货单item(ID:"+stockOutFormItem1.getId()+")状态为："+stockOutFormItem.getStatus()+"，不能操作");
//                }

            }






            /*************二、更新订单order_item(同订单所有items)状态为待拣货0***************/
            jdbcTemplate.update("UPDATE "+Tables.ErpOrderItem + " SET  status=0 WHERE orderId=? AND status=1",orderId);


            /*************三、更新订单order状态为拣货中***************/
            jdbcTemplate.update("UPDATE "+Tables.ErpOrder + " SET  status=0 WHERE id=? AND status=1",orderId);

            /*************四、更新拣货单状态***************/
            var isDaiChuKu = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM " + Tables.ErpStockOutFormItem + " WHERE formId=? AND status <> 3 AND status <> 99", int.class, stockOutFormItem.getFormId());
            if(isDaiChuKu == 0 ){
                //全部出库了，更新状态为已出库
                //更新拣货单状态
                jdbcTemplate.update("UPDATE " + Tables.ErpStockOutForm + " SET status=?,completeTime=?,stockOutUserId=?,stockOutUserName=? WHERE id=?",
                        StockOutFormStatusEnum.OUTED.getIndex(),
                        System.currentTimeMillis() / 1000,
                        userId, userName,
                        stockOutFormItem.getFormId());

            }

//            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");

        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.SystemException, "系统异常"+e.getMessage());
        }

    }


    /**
     * 确认拣货单，更新拣货单状态为已拣货
     *
     * @param stockOutFormId 拣货单id
     * @param itemList       拣货明细list
     * @param userId         当前操作的userid
     * @param userName       当前操作人
     * @return
     */
    @Transactional
    public Integer completePicking(Long stockOutFormId, List<ErpStockOutPickVo> itemList, Integer userId, String userName) {
        ErpStockOutFormEntity stockOutForm = null;
        try {
            String stockOutFormSQL = "SELECT * FROM " + Tables.ErpStockOutForm + " where id=?";
            stockOutForm = jdbcTemplate.queryForObject(stockOutFormSQL, new BeanPropertyRowMapper<>(ErpStockOutFormEntity.class), stockOutFormId);
            if (stockOutForm.getStatus().intValue() != StockOutFormStatusEnum.Packing.getIndex()) return 0;
        } catch (Exception e) {
            return -404;//拣货单不存在
        }


        /************检查数据*************/
        for (var item : itemList) {
            //查询仓位库存信息
            var goodsStockInfos = jdbcTemplate.query("SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE specId=? AND locationId=? AND isDelete = 0"
                    , new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), item.getSkuId(), item.getLocationId());
            if (goodsStockInfos == null || goodsStockInfos.size() == 0) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return -405;//仓位库存信息不存在
            }
            ErpGoodsStockInfoEntity stockInfo = goodsStockInfos.get(0);


            //查询商品对应的可用库存
            Long availableStock = goodsStockInfos.get(0).getCurrentQty().longValue();// - goodsStockInfos.get(0).getLockedQty().longValue();
            //判断库存
            if (availableStock.longValue() < item.getQuantity().longValue()) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
                return -406;//仓位可用库存不足
            }


            /************开始处理拣货出库*************/

            try {
                //查出拣货单明细数据
                var stockOutFormItemList = jdbcTemplate.query("SELECT * FROM " + Tables.ErpStockOutFormItem + " WHERE formId=? AND specId=?"
                        , new BeanPropertyRowMapper<>(ErpStockOutFormItemEntity.class), stockOutFormId, item.getSkuId());


                log.info("获取到拣货单明细中SKU：" + item.getSkuId() + "有" + stockOutFormItemList.size() + "条记录，");

                for (var formItem : stockOutFormItemList) {

                    /************更新拣货单item 状态 **************/
                    jdbcTemplate.update("UPDATE " + Tables.ErpStockOutFormItem + " SET status=2,locationId=?,pickedTime=?,completeTime=? WHERE id=? and (`status` = 1 OR `status` = 0) "
                            , item.getLocationId(), System.currentTimeMillis() / 1000, System.currentTimeMillis() / 1000, formItem.getId());

                    log.info("更新stock_out_item数据。{locationId:" + item.getLocationId() + ",itemId:" + formItem.getId() + ",SKU:" + formItem.getSpecNum() + "}");


                    //去除锁定库存 ErpGoodsStockInfo
                    jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty-?,lockedQty=lockedQty-? WHERE id=?", formItem.getQuantity(), formItem.getQuantity(), stockInfo.getId());

                    var goodsStockInfoItems = jdbcTemplate.query("SELECT * FROM erp_goods_stock_info_item WHERE stockInfoId=? and currentQty>0 ", new BeanPropertyRowMapper<>(ErpGoodsStockInfoItemEntity.class), stockInfo.getId());
                    /**********处理先进先出问题**********/
                    if (goodsStockInfoItems.size() > 0) {
                        int quantity = formItem.getQuantity();//购买数量

                        ErpGoodsStockInfoItemEntity stockInfoItem = goodsStockInfoItems.get(0);
                        //数量小于库存 库存-数量
                        if (stockInfoItem.getCurrentQty().intValue() >= quantity) {
                            //更新goodsStockInfoItem
                            jdbcTemplate.update("update erp_goods_stock_info_item set currentQty=currentQty-? where id=?", quantity, stockInfoItem.getId());

                            //插入erp_stock_out_form_item_detail数据
                            jdbcTemplate.update("INSERT INTO erp_stock_out_form_item_detail (stock_out_form_item_id,goods_stock_info_id,goods_stock_info_item_id,quantity) VALUE (?,?,?,?)"
                                    , formItem.getId(),stockInfoItem.getStockInfoId(),stockInfoItem.getId(),quantity);

                        } else {
                            Long cutQty = 0L;//已减库存
                            /***循环更新出库批次***/
                            for (var stockItem : goodsStockInfoItems) {
                                cutQty += stockItem.getCurrentQty();
                                if (quantity > cutQty) {
                                    jdbcTemplate.update("update erp_goods_stock_info_item set currentQty=currentQty-? where id=?", stockItem.getCurrentQty(), stockItem.getId());
                                    //插入erp_stock_out_form_item_detail数据
                                    jdbcTemplate.update("INSERT INTO erp_stock_out_form_item_detail (stock_out_form_item_id,goods_stock_info_id,goods_stock_info_item_id,quantity) VALUE (?,?,?,?)"
                                            , formItem.getId(),stockItem.getStockInfoId(),stockItem.getId(),stockItem.getCurrentQty());

                                } else {
                                    Long cutQty_ = quantity - (cutQty - stockItem.getCurrentQty());
                                    jdbcTemplate.update("update erp_goods_stock_info_item set currentQty=currentQty-? where id=?", cutQty_, stockItem.getId());
                                    //插入erp_stock_out_form_item_detail数据
                                    jdbcTemplate.update("INSERT INTO erp_stock_out_form_item_detail (stock_out_form_item_id,goods_stock_info_id,goods_stock_info_item_id,quantity) VALUE (?,?,?,?)"
                                            , formItem.getId(),stockItem.getStockInfoId(),stockItem.getId(),cutQty_);

                                    break;
                                }
                            }
                        }
                    }else
                    {
                        log.error("没有找到批次信息");
                    }


                    /************更新拣货单item对应的订单item 状态:2已出库 **************/
                    //'XIAOHONGSHU','DOUDIAN','ERP'
                    if(formItem.getSourceChannel().equals("XIAOHONGSHU")) {
                        //小红书店铺
                        try {
                            var d = jdbcTemplate.queryForObject("SELECT * FROM dc_xhs_order_item WHERE id=?",new BeanPropertyRowMapper<>(XhsOrderItemEntity.class), formItem.getSourceOrderItemId());
                            jdbcTemplate.update("UPDATE dc_xhs_order_item SET erpSendStatus=2 WHERE id=?", formItem.getSourceOrderItemId());
                            int c = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM dc_xhs_order_item WHERE erpSendStatus<>2 AND orderId=? ",int.class,d.getOrderId());
                            if(c == 0){
                                jdbcTemplate.update("UPDATE dc_xhs_order SET erpSendStatus=2 WHERE id=?",d.getOrderId());
                            }
                        }catch (Exception e){
                            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
                        }
                    }

//                    /************更新拣货单item对应的订单item 状态 **************/
//                    jdbcTemplate.update("UPDATE "+Tables.ErpOrderItem + " SET status=2,outLocationId=? WHERE id=? and `status` = 1",item.getLocationId(),formItem.getItemId());
//                    log.info("更新order_item数据。{locationId:"+item.getLocationId()+",itemId:"+formItem.getItemId()+"}");

                    /*************  2.3、加入库存日志erp_goods_stock_logs*****************/
                    String outType = "";//1订单拣货出库2采购退货出库3盘点出库4报损出库
                    if (stockOutForm.getOutType() == 1) outType = "订单拣货出库";
                    if (stockOutForm.getOutType() == 2) outType = "采购退货出库";
                    if (stockOutForm.getOutType() == 3) outType = "盘点出库";
                    if (stockOutForm.getOutType() == 4) outType = "报损出库";
                    String remark = outType + "，出库单号:" + stockOutForm.getStockOutNo();
                    remark += " SKU：" + formItem.getSpecNum();
                    remark += " 数量：" + formItem.getQuantity();
                    remark += " 仓位ID：" + item.getLocationId();
                    remark += "操作人:" + userId + "，" + userName;
                    String logsSQL = "INSERT INTO " + Tables.ErpGoodsStockLogs +
                            " (goodsId,goodsNumber,specId,specNumber,locationId,quantity,createTime,`type`,sourceType,sourceId,remark,createOn,currQty) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    jdbcTemplate.update(logsSQL,
                            stockInfo.getGoodsId()
                            , stockInfo.getGoodsNumber()
                            , formItem.getSpecId()
                            , formItem.getSpecNum()
                            , item.getLocationId(), formItem.getQuantity(),
                            new Date(), EnumGoodsStockLogType.OUT.getIndex(), EnumGoodsStockLogSourceType.OrderSend.getIndex(), formItem.getId()
                            , remark, System.currentTimeMillis() / 1000, goodsStockInfos.get(0).getCurrentQty().longValue() - item.getQuantity().longValue());
                }


            } catch (Exception e) {
                //系统异常
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return -500;
            }
        }
        /*****1、更新拣货单erp_stock_out_form状态*****/
//        jdbcTemplate.update("UPDATE " + Tables.ErpStockOutForm + " SET status=?,completeTime=? WHERE id=?", StockOutFormStatusEnum.Picked.getIndex(), System.currentTimeMillis() / 1000, stockOutFormId);


        /*****1、更新拣货单erp_stock_out_form状态*****/
        int count1 = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM " + Tables.ErpStockOutFormItem + " WHERE formId=? AND (status = 0 OR  status = 1)", Integer.class, stockOutFormId);
        if (count1 == 0) {
            //更新拣货单状态
            jdbcTemplate.update("UPDATE " + Tables.ErpStockOutForm + " SET status=?,completeTime=?,stockOutUserId=?,stockOutUserName=? WHERE id=?",
                    StockOutFormStatusEnum.Picked.getIndex(),
                    System.currentTimeMillis() / 1000,
                    userId, userName,
                    stockOutFormId);
            //TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }


        //更新拣货单状态
//        jdbcTemplate.update("UPDATE " + Tables.ErpStockOutForm + " SET status=?,completeTime=?,stockOutUserId=?,stockOutUserName=? WHERE id=?",
//                StockOutFormStatusEnum.Picked.getIndex(),
//                System.currentTimeMillis() / 1000,
//                userId, userName,
//                stockOutFormId);
        //TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚事务
        return 1;

    }

    public List<ErpOrderItemEntity> outList (String startDate, String endDate){
        StringBuilder sb =new StringBuilder("SELECT e.stockOutNo,i.quantity,i.completeTime printQty, item.productNumber,item.skuNumber,item.itemAmount,\n" +
                "(case  WHEN item.purPrice>0 THEN item.purPrice ELSE (SELECT IFNULL(price,0) from erp_invoice_info WHERE specId= item.skuId and price>0 ORDER BY id desc LIMIT 1 ) END ) purPrice\n" +
                "from erp_stock_out_form_item i LEFT JOIN erp_stock_out_form e ON i.formId=e.id LEFT JOIN erp_order_item item ON i.itemId=item.id\n" +
                "LEFT JOIN erp_order eo ON eo.id= item.orderId\n" +
                " WHERE e.outType=1 AND eo.shopId=8 ");
        List<Object> params = new ArrayList<>();
        if(!StringUtils.isEmpty(startDate)) {
            //按一级大类查询
            sb.append(" AND FROM_UNIXTIME(i.completeTime,'%Y-%m-%d') > ? ");
            params.add(startDate);
        }

        if(!StringUtils.isEmpty(endDate)) {
            //按一级大类查询
            sb.append(" AND FROM_UNIXTIME(i.completeTime,'%Y-%m-%d') <= ?");
            params.add(endDate);
        }
        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ErpOrderItemEntity.class), params.toArray(new Object[params.size()]));
        return list;
    }


    /**
     * 按订单批量生成拣货单
     *
     * @param sourceNo 来源订单号
     *  渠道'XIAOHONGSHU','DOUDIAN','ERP'
     * @return
     */
    @Transactional
    public ResultVo<Long> joinStockOutQueueForXHS(String sourceNo, Long orderId, String createBy, List<StockDataVo> stockDataVos,String companyCode,String expressNo) {
        try {
            /************ 判断订单商品库存 ************/
            for (var item : stockDataVos) {
                // 查询库存
                int keyongQty = erpGoodsRepository.getGoodsSpecStockById(item.getSpecId());

                if (keyongQty < item.getQuantity().intValue()) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return new ResultVo<>(EnumResultVo.DataError, item.getSpecNum() + "可用库存不足");
                }
            }


            /************ 1、生成拣货单信息 *************/
            // 生成拣货单号
            String no = "OUT" + OrderNumberUtils.getOrderIdByTime();

            /********** 添加拣货单表erp_stock_out_form单据数据 ***********/
            String formSQL = "INSERT INTO " + Tables.ErpStockOutForm
                    + " (stockOutNo,sourceNo,sourceId,sourceChannel,outType,status,printStatus,remark,createBy) VALUE (?,?,?,?,?,?,?,?,?)";
            String remark = "小红书订单加入拣货队列，订单ID：" + orderId;

            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(formSQL, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, no);
                    ps.setString(2, sourceNo);
                    ps.setLong(3, orderId);
                    ps.setString(4, "XIAOHONGSHU");
                    ps.setInt(5, 1);// 出库类型1订单拣货出库2采购退货出库
                    ps.setInt(6, StockOutFormStatusEnum.WAIT.getIndex());
                    ps.setInt(7, 0);
                    ps.setString(8, remark);
                    ps.setString(9, createBy);
                    return ps;
                }
            }, keyHolder);

            Long stockOutId = keyHolder.getKey().longValue();
            for (var i : stockDataVos) {
                /****查询库存仓位和批次，以便写进出库单 *****/
                String stockInfoSQL ="SELECT gsi.*,sl.`name` as locationName FROM "+Tables.ErpGoodsStockInfo+" as gsi LEFT JOIN "+Tables.ErpStockLocation+" as sl on sl.id=gsi.locationId WHERE gsi.specId=? AND gsi.isDelete=0 AND gsi.currentQty>gsi.lockedQty ";

                List<ErpGoodsStockInfoEntity> stockInfoList = jdbcTemplate.query(stockInfoSQL,new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),i.getSpecId());

                if(stockInfoList.get(0).getCurrentQty().longValue() - stockInfoList.get(0).getLockedQty().longValue() < i.getQuantity()){

                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();

                    return new ResultVo<>(EnumResultVo.DataError, "商品"+i.getSpecNum() + "仓位"+stockInfoList.get(0).getLocationName()+"可用库存不足");
                }
                ErpGoodsStockInfoEntity gsi = stockInfoList.get(0);
                // 拣货单明细不存在，添加
                /************* 一、添加拣货单明细 stock_out_form_item ***************/
                String formItemSQL = "INSERT INTO " + Tables.ErpStockOutFormItem
                        + " (formId,sourceOrderitemId,goodsId,specId,specNum,locationId,quantity,status,sourceChannel,sourceOrderId,sourceOrderNo) VALUE (?,?,?,?,?,?,?,?,?,?,?)";

                KeyHolder keyHolderFormItem = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection)
                            throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(formItemSQL,
                                Statement.RETURN_GENERATED_KEYS);
                        ps.setLong(1, stockOutId);
                        ps.setLong(2, i.getOrderItemId());
                        ps.setInt(3, gsi.getGoodsId());
                        ps.setInt(4, i.getSpecId());
                        ps.setString(5, i.getSpecNum());
                        ps.setInt(6, gsi.getLocationId());
                        ps.setInt(7, i.getQuantity());
                        ps.setInt(8, StockOutFormStatusEnum.WAIT.getIndex());
                        ps.setString(9,"XIAOHONGSHU");
                        ps.setLong(10,orderId);
                        ps.setString(11,sourceNo);

                        return ps;
                    }
                }, keyHolderFormItem);

                /****更新锁定库存****/
                //查询库存信息LIST
//                List<ErpGoodsStockInfoEntity> stockInfoEntityList = jdbcTemplate.query("SELECT * FROM "+Tables.ErpGoodsStockInfo +" WHERE specId=? AND isDelete=0",new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),i.getSpecId());
                int shengyuQuantity = i.getQuantity();
                int stockInfoEntityListIndex = 0;
                while (shengyuQuantity > 0){
                    //当前信息中可用库存
                    int t = stockInfoList.get(stockInfoEntityListIndex).getCurrentQty().intValue() - stockInfoList.get(stockInfoEntityListIndex).getLockedQty().intValue();

                    if(t - shengyuQuantity > 0){
                        jdbcTemplate.update("UPDATE "+Tables.ErpGoodsStockInfo + "SET lockedQty=lockedQty+? WHERE id=?",shengyuQuantity,stockInfoList.get(stockInfoEntityListIndex).getId());
                        shengyuQuantity = 0;
                    }else{
                        jdbcTemplate.update("UPDATE "+Tables.ErpGoodsStockInfo + "SET lockedQty=lockedQty+? WHERE id=?",t,stockInfoList.get(stockInfoEntityListIndex).getId());
                        shengyuQuantity = shengyuQuantity-t;
                    }
                }
                /*****************更新小红书订单ITEM状态为：出库中********************/
                //ERP发货状态0待处理1出库中2已出库3已发货
                jdbcTemplate.update("UPDATE dc_xhs_order_item SET erpSendStatus=1 WHERE id=?",i.getOrderItemId());


            }

            /*****************更新小红书订单状态为：出库中********************/
            jdbcTemplate.update("UPDATE dc_xhs_order SET erpSendStatus=1,expressTrackingNo=?,expressCompanyCode=? WHERE id=?", expressNo,companyCode, orderId);

//            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();

            return new ResultVo<>(EnumResultVo.SUCCESS, stockOutId);
        }catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.SystemException, "系统异常"+e.getMessage());
        }

    }


    /**
     * 获取待拣货商品list
     * @param pageIndex
     * @param pageSize
     * @return
     */
    @Transactional
    public PagingResponse<ErpStockOutFormItemEntity> getStockOutFormItemWaitPickGoodsList(Integer pageIndex, Integer pageSize,String specNum) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT SQL_CALC_FOUND_ROWS ");
//        sql.append(" sofi.sourceChannel,sofi.sourceOrderId,sofi.sourceOrderNo,sofi.specId,sofi.specNum,sofi.quantity");
        sql.append(" SUM(sofi.quantity) AS quantity");
        sql.append(" ,gs.color_value,gs.color_image,size_value,gs.specNumber as specNum,g.id as goodsId,g.`number` as goodsNumber,g.name as goodsName");
        sql.append(" FROM erp_stock_out_form_item sofi ");
        sql.append( " LEFT JOIN erp_goods_spec gs ON gs.id = sofi.specId ");
        sql.append( " LEFT JOIN erp_goods g ON g.id = gs.goodsId ");
        sql.append(" WHERE sofi.`status`= 0 ");
        sql.append(" GROUP BY sofi.specId ");


        List<Object> params = new ArrayList<>();

        if (StringUtils.isEmpty(specNum) == false) {
            sql.append(" AND gs.specNumber=? ");
            params.add(specNum);
        }
        sql.append(" ORDER BY sofi.specId DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ErpStockOutFormItemEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * 小红书发货
     * @param orderId
     * @param companyCode
     * @param expressNo
     * @return
     */
    public ResultVo<Long> sendOrderXHS(Long orderId, String companyCode, String expressNo) {
        try {
            var order = jdbcTemplate.queryForObject("SELECT * FROM dc_xhs_order WHERE id=?", new BeanPropertyRowMapper<>(XhsOrderEntity.class), orderId);
            if(order.getOrderStatus()!=4) return new ResultVo<>(EnumResultVo.DataError, "不能发货，订单状态："+ EnumXhsOrderStatus.getStatusName(order.getOrderStatus()));
            else if (order.getAfterSalesStatus() != 1) return new ResultVo<>(EnumResultVo.DataError, "不能发货，订单正在售后中");
            else if(order.getCancelStatus() != 0)return new ResultVo<>(EnumResultVo.DataError, "不能发货，订单正在取消中");

            //更新
            jdbcTemplate.update("UPDATE dc_xhs_order SET orderStatus=6,deliveryTime=?,expressTrackingNo=?,expressCompanyCode=? WHERE id=?",System.currentTimeMillis(),expressNo,companyCode,orderId);
            return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.SystemException, "系统异常"+e.getMessage());
        }
    }
}
